import pymysql
import xlrd
import os


def xls_to_mysql():

    share_bike_db = pymysql.connect('localhost','root','minicooper','share_bike',charset='utf8')
    cursor = share_bike_db.cursor()

    create_sql = """CREATE TABLE IF NOT EXISTS BIKE_INFO (
                    TITLE TEXT NOT NULL,
                    INFO_PROPERTY TEXT,
                    ORIGINA_OR_NOT TEXT,
                    ADDR TEXT,
                    PRESS_NAME TEXT,
                    NICKNAME TEXT,
                    PUB_DATE TEXT,
                    PRE_TYPE TEXT,
                    ORIGNAL_CONTENT TEXT,
                    QUALIFY_TYPE TEXT,
                    ADD_AREA TEXT,
                    FANS INT,
                    GENDER TEXT,
                    WEIBOS TEXT,
                    POST INT,
                    COMMENT INT,
                    LIKES INT,
                    VIEWS INT,
                    CONTENT_CUT TEXT,
                    FULL_CONTENT TEXT,
                    AUTHOR_ID TEXT)
                    """
    cursor.execute(create_sql)
    share_bike_db.commit()

    path = "d:\\6-28共享单车文本数据\\"
    for file in os.listdir(path):
        full_path = path + file
        data = xlrd.open_workbook(full_path)
        table = data.sheets()[0]
        rows = table.nrows

        for i in range(1,rows):
            title = table.cell(i, 0).value
            info_property = table.cell(i, 1).value
            original_or_not = table.cell(i, 2).value
            addr = table.cell(i, 3).value
            press_name = table.cell(i, 4).value
            nickname = table.cell(i, 5).value
            pub_date = table.cell(i, 6).value
            pre_type = table.cell(i, 7).value
            original_content = table.cell(i, 8).value
            quality_type = table.cell(i, 9).value
            add_area = table.cell(i, 10).value
            fans = 0 if table.cell(i, 11).value == '' else int(table.cell(i, 11).value)
            gender = table.cell(i, 12).value
            weibos = table.cell(i, 13).value
            post = 0 if table.cell(i, 14).value == '' else int(table.cell(i, 14).value)
            comment = 0 if table.cell(i, 15).value == '' else int(table.cell(i, 15).value)
            likes = 0 if table.cell(i, 16).value == '' else int(table.cell(i, 16).value)
            views = 0 if table.cell(i, 17).value == '' else int(table.cell(i, 17).value)
            content_cut = table.cell(i, 18).value
            full_content = table.cell(i, 19).value
            author_id = table.cell(i, 20).value
            insert_data = (title,info_property,original_or_not,addr,press_name,nickname,pub_date,pre_type,original_content,quality_type,add_area,fans,gender,weibos,post,comment,likes,views,content_cut,full_content,author_id)
            print(insert_data)
            insert_sql = "INSERT INTO BIKE_INFO (TITLE,INFO_PROPERTY,ORIGINA_OR_NOT,ADDR,PRESS_NAME,NICKNAME,PUB_DATE,PRE_TYPE,ORIGNAL_CONTENT,QUALIFY_TYPE,ADD_AREA,FANS,GENDER,WEIBOS,POST,COMMENT,LIKES,VIEWS,CONTENT_CUT,FULL_CONTENT,AUTHOR_ID) VALUES {0}".format(insert_data)
            cursor.execute(insert_sql)
        share_bike_db.commit()
    share_bike_db.close()


if __name__ == '__main__':
    xls_to_mysql()